﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using MagicBag.CRM.Model;
using MagicBag.CRM.IDAL;
using MagicBag.CRM.Common;
namespace MagicBag.CRM.SqlServerDAL
{
    public class CommonDictionaryDAL : ICommonDictionary
    {
        public int Insert(CommonDictionary dic)
        {
            string sql = "insert into [dbo].[CommonDictionary] ([Category] ,[Name] ,[Value] , [Description],[ParentID]) values(@Category,@Name,@Value,@Description,@ParentID)";
            SqlParameter[] paras = new SqlParameter[5];
            paras[0] = new SqlParameter("@Category", SqlDbType.VarChar, 100);
            paras[1] = new SqlParameter("@Name", SqlDbType.VarChar, 100);
            paras[2] = new SqlParameter("@Value", SqlDbType.VarChar, 100);
            paras[3] = new SqlParameter("@Description", SqlDbType.VarChar, 100);
            paras[4] = new SqlParameter("@ParentID", SqlDbType.Int);

            paras[0].Value = dic.Category;
            paras[1].Value = dic.Name;
            paras[2].Value = dic.Value;
            paras[3].Value = dic.Description;
            if (dic.ParentID == 0)
                paras[4].Value = DBNull.Value;
            else
            paras[4].Value = dic.ParentID;

            using (SqlConnection conn = new SqlConnection(SqlHelper.baseConnectionString))
            {
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, paras);
            }
        }

        public int Delete(int id)
        {
            string sql = "delete from [dbo].[CommonDictionary] where [ID]=@ID";
            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@ID", SqlDbType.Int);
            paras[0].Value = id;
            using (SqlConnection conn = new SqlConnection(SqlHelper.baseConnectionString))
            {
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, paras);
            }
        }

        public int Update(CommonDictionary dic)
        {
            string sql = "update [dbo].[CommonDictionary] set[Category]= @Category,[Name]=@Name,[Value]=@Value,[Description]=@Description,[ParentID]=@ParentID where [ID]=@ID";
            SqlParameter[] paras = new SqlParameter[6];
            paras[0] = new SqlParameter("@Category", SqlDbType.VarChar, 100);
            paras[1] = new SqlParameter("@Name", SqlDbType.VarChar, 100);
            paras[2] = new SqlParameter("@Value", SqlDbType.VarChar, 100);
            paras[3] = new SqlParameter("@Description", SqlDbType.VarChar, 100);
            paras[4] = new SqlParameter("@ParentID", SqlDbType.Int);
            paras[5] = new SqlParameter("@ID", SqlDbType.Int);

            paras[0].Value = dic.Category;
            paras[1].Value = dic.Name;
            paras[2].Value = dic.Value;
            paras[3].Value = dic.Description;
            if (dic.ParentID != 0)
                paras[4].Value = dic.ParentID;
            else
                paras[4].Value = DBNull.Value;
            paras[5].Value = dic.ID;
            using (SqlConnection conn = new SqlConnection(SqlHelper.baseConnectionString))
            {
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, paras);
            }
        }

        public CommonDictionary GetCommonDictionary(int id)
        {
            throw new NotImplementedException();
        }

        public IList<CommonDictionary> GetCommonDictionary(string category)
        {
            throw new NotImplementedException();
        }

        public IList<CommonDictionary> GetCommonDictionaries()
        {
            IList<CommonDictionary> list = new List<CommonDictionary>();

            string sql = "select  [ID] ,[Category] ,[Name] ,isnull([Value],'') [Value] ,isnull([Description],'') [Description],isnull([ParentID],0) [ParentID]  FROM [dbo].[CommonDictionary]";

            using (SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.baseConnectionString, CommandType.Text, sql, null))
            {
                while (sdr.Read())
                {
                    CommonDictionary dic = new CommonDictionary();
                    dic.ID = sdr.GetInt32(0);
                    dic.Category = sdr.GetString(1);
                    dic.Name = sdr.GetString(2);
                    dic.Value = sdr.GetString(3);
                    dic.Description = sdr.GetString(4);
                    dic.ParentID = sdr.GetInt32(5);
                    list.Add(dic);
                }
            }
            return list;
        }

        public IList<CommonDictionary> GetCommonDictionaries(int pageIndex, int pageSize, out int total)
        {
            throw new NotImplementedException();
        }


        public IList<CommonDictionary> GetCategories()
        {
            IList<CommonDictionary> list = new List<CommonDictionary>();

            string sql = "select distinct [Category] FROM [dbo].[CommonDictionary]";

            using (SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.baseConnectionString, CommandType.Text, sql, null))
            {
                while (sdr.Read())
                {
                    CommonDictionary dic = new CommonDictionary();
                    dic.Category = sdr.GetString(0);
                    list.Add(dic);
                }
            }
            return list;
        }
    }
}
